﻿Imports AugmentLib.Database
Imports System.Configuration.ConfigurationManager
Imports AugmentLib.RDLC
Imports Microsoft.Reporting.WebForms



Partial Class rptMonthlyReport
    Inherits System.Web.UI.Page

    Private Function LoadMonthlyReport(ByRef GrandTotal As Decimal) As Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        Dim TempDT As New Data.DataTable

        Dim strWhere As String

        Dim dtFromDate As DateTime
        Dim dtToDate As DateTime
        'to add in filtering for report - fromDate, toDate, Type(Created;Updated)
        If txtFromDate.Text <> "" And txtToDate.Text <> "" Then
            dtFromDate = Date.ParseExact(txtFromDate.Text, "dd-MMM-yyyy", Nothing)
            dtToDate = Date.ParseExact(txtToDate.Text, "dd-MMM-yyyy", Nothing)
            If dtFromDate > dtToDate Then
                lblMsgError.Text = "Date To cannot be earlier than Date From!"
                divError.Visible = True
                Return Nothing
                Exit Function
            Else
                If ddlStaffName.SelectedValue = "" Then '--All
                    strWhere = ""
                Else
                    strWhere = " AND (st.ListerId=@StaffId OR st.TransactorId=@StaffId)"
                    objDB.AddParameter("StaffId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlStaffName.SelectedValue))
                End If
                objDB.AddParameter("DateFrom", Data.SqlDbType.DateTime, dtFromDate)
                objDB.AddParameter("DateTo", Data.SqlDbType.DateTime, dtToDate.AddDays(1))
                objDB.OpenConnection()
                objDB.dbVariable.SQLString = "SELECT st.CollectCompleteDate, p.UnitNumber + ', ' + d.Road + " & _
                                        "', ' + o.Location + ', ' + s.State + ', ' + y.Country as 'Address', " & _
                                        "CASE st.TransListType WHEN '1' THEN 'SR' WHEN '2' THEN 'SC' WHEN '3' " & _
                                        "THEN 'SL' WHEN '4' THEN 'SI' ELSE '' END as Cat, st.ListerId, st.TransactorId, " & _
                                        "l.Initial as Lister, t.Initial as Transactor, st.AgencyFee, st.GovAmt, " & _
                                        "st.GstPaidBy, st.DisbursementFee, 0 as TotalTA, " & _
                                        "0 as StampDuty, st.ListerAmt, st.ReferralAmt FROM AW_SaleTransaction st " & _
                                        "LEFT JOIN AW_Property p ON st.PropertyId = p.PropertyId " & _
                                        "LEFT JOIN AW_Road d ON p.RoadId = d.RoadId LEFT JOIN AW_Location o " & _
                                        "ON d.LocationId = o.LocationId LEFT JOIN AW_State s ON o.StateId = s.StateId " & _
                                        "LEFT JOIN AW_Country y ON s.CountryId=y.CountryId LEFT JOIN AW_Staff l " & _
                                        "ON st.ListerId = l.StaffId LEFT JOIN AW_Staff t " & _
                                        "ON st.TransactorId = t.StaffId WHERE st.Status in ('3','4') " & _
                                        "AND st.UndoDate IS NULL AND st.CollectComplete = 1 " & _
                                        "AND st.CollectCompleteDate BETWEEN @DateFrom AND @DateTo " & strWhere & "UNION " & _
                                        "SELECT st.CollectCompleteDate, p.UnitNumber + ', ' + d.Road + ', ' + o.Location + " & _
                                        "', ' + s.State + ', ' + y.Country as 'Address', " & _
                                        "CASE st.TransListType WHEN '1' THEN 'RR' WHEN '2' THEN 'RC' WHEN '3' " & _
                                        "THEN 'RL' WHEN '4' THEN 'RI' ELSE '' END as Cat, st.ListerId, st.TransactorId, " & _
                                        "l.Initial as Lister, t.Initial as Transactor, st.AgencyFee, st.GovAmt, st.GstPaidBy, " & _
                                        "st.DisbursementFee, st.TotalTA, st.StampDuty, st.ListerAmt, st.ReferralAmt " & _
                                        "FROM AW_RentTransaction st LEFT JOIN AW_Property p ON " & _
                                        "st.PropertyId = p.PropertyId LEFT JOIN AW_Road d ON p.RoadId = d.RoadId " & _
                                        "LEFT JOIN AW_Location o ON d.LocationId = o.LocationId LEFT JOIN AW_State s " & _
                                        "ON o.StateId = s.StateId LEFT JOIN AW_Country y ON s.CountryId=y.CountryId " & _
                                        "LEFT JOIN AW_Staff l ON st.ListerId = l.StaffId LEFT JOIN AW_Staff t " & _
                                        "ON st.TransactorId = t.StaffId WHERE st.Status in ('3','4') AND " & _
                                        "st.UndoDate IS NULL AND st.CollectComplete = 1 AND st.CollectDate " & _
                                        "BETWEEN @DateFrom AND @DateTo " & strWhere
                objDB.FillData(TempDT, "AW_MonthlyRenReport", Database.SQLCommandType.NormalString)
                objDB.CloseConnection()
                'Massage data before push to RDLC
                'Declare Table and column
                Dim MyDT As New Data.DataTable
                Dim Name As Data.DataColumn = New Data.DataColumn("Name")
                Name.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(Name)  '0
                Dim Address As Data.DataColumn = New Data.DataColumn("Address")
                Address.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(Address)   '1
                Dim Cat As Data.DataColumn = New Data.DataColumn("Cat")
                Cat.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(Cat)   '2
                Dim AgencyFee As Data.DataColumn = New Data.DataColumn("AgencyFee")
                AgencyFee.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(AgencyFee) '3
                Dim DisbursementFee As Data.DataColumn = New Data.DataColumn("DisburesementFee")
                DisbursementFee.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(DisbursementFee)   '4
                Dim TAS As Data.DataColumn = New Data.DataColumn("TAS")
                TAS.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(TAS)   '5
                Dim ListerAmt As Data.DataColumn = New Data.DataColumn("ListerAmt")
                ListerAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(ListerAmt) '6
                Dim GovAmt As Data.DataColumn = New Data.DataColumn("GovAmt")
                GovAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(GovAmt)    '7
                Dim PListerAmt As Data.DataColumn = New Data.DataColumn("PListerAmt")
                PListerAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(PListerAmt)    '8
                Dim SDuty As Data.DataColumn = New Data.DataColumn("SDuty")
                SDuty.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(SDuty) '9
                Dim Reff As Data.DataColumn = New Data.DataColumn("Reff")
                Reff.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(Reff)  '10
                Dim PGovAmt As Data.DataColumn = New Data.DataColumn("PGovAmt")
                PGovAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(PGovAmt)   '11
                Dim UId As Data.DataColumn = New Data.DataColumn("UId")
                UId.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(UId)   '12
                Dim Commission As Data.DataColumn = New Data.DataColumn("Commission")
                Commission.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add("Commission")  '13
                Dim MyRow As Data.DataRow
                For Each dr As Data.DataRow In TempDT.Rows
                    If dr.Item("Lister").ToString = dr.Item("Transactor").ToString Then
                        MyRow = MyDT.NewRow()
                        MyRow(0) = dr.Item("Lister").ToString
                        MyRow(1) = dr.Item("Address").ToString
                        MyRow(2) = dr.Item("Cat").ToString
                        MyRow(3) = CDbl(dr.Item("AgencyFee").ToString)
                        MyRow(4) = CDbl(dr.Item("DisbursementFee").ToString)
                        MyRow(5) = CDbl(dr.Item("TotalTA").ToString) + CDbl(dr.Item("StampDuty").ToString)
                        MyRow(6) = 0
                        If dr.Item("GstPaidBy").ToString = "1" Then
                            MyRow(7) = CDbl(dr.Item("GovAmt").ToString)
                        Else
                            MyRow(7) = 0
                        End If
                        MyRow(8) = 0
                        MyRow(9) = CDbl(dr.Item("StampDuty").ToString)
                        MyRow(10) = CDbl(dr.Item("ReferralAmt").ToString)
                        MyRow(11) = CDbl(dr.Item("GovAmt").ToString)
                        MyRow(12) = dr.Item("ListerId").ToString
                        MyRow(13) = 0
                        MyDT.Rows.Add(MyRow)
                    Else
                        'add as lister
                        MyRow = MyDT.NewRow()
                        MyRow(0) = dr.Item("Lister").ToString
                        MyRow(1) = dr.Item("Address").ToString
                        MyRow(2) = dr.Item("Cat").ToString
                        MyRow(3) = 0
                        MyRow(4) = 0
                        MyRow(5) = 0
                        MyRow(6) = CDbl(dr.Item("ListerAmt").ToString)
                        MyRow(7) = 0
                        MyRow(8) = 0
                        MyRow(9) = 0
                        MyRow(10) = 0
                        MyRow(11) = 0
                        MyRow(12) = dr.Item("ListerId").ToString
                        MyRow(13) = 0
                        MyDT.Rows.Add(MyRow)
                        'add as transactor
                        MyRow = MyDT.NewRow()
                        MyRow(0) = dr.Item("Transactor").ToString
                        MyRow(1) = dr.Item("Address").ToString
                        MyRow(2) = dr.Item("Cat").ToString
                        MyRow(3) = CDbl(dr.Item("AgencyFee").ToString)
                        MyRow(4) = CDbl(dr.Item("DisbursementFee").ToString)
                        MyRow(5) = CDbl(dr.Item("TotalTA").ToString) + CDbl(dr.Item("StampDuty").ToString)
                        MyRow(6) = CDbl(dr.Item("ListerAmt").ToString)
                        If dr.Item("GstPaidBy").ToString = "1" Then
                            MyRow(7) = CDbl(dr.Item("GovAmt").ToString)
                        Else
                            MyRow(7) = 0
                        End If
                        MyRow(8) = CDbl(dr.Item("ListerAmt").ToString)
                        MyRow(9) = CDbl(dr.Item("StampDuty").ToString)
                        MyRow(10) = CDbl(dr.Item("ReferralAmt").ToString)
                        MyRow(11) = CDbl(dr.Item("GovAmt").ToString)
                        MyRow(12) = dr.Item("TransactorId").ToString
                        MyRow(13) = 0
                        MyDT.Rows.Add(MyRow)
                    End If
                Next
                MyDT.TableName = "AW_MonthlyReport"
                'Process calculation and get commission percentage
                Dim dtView As Data.DataView = New Data.DataView
                With dtView
                    .Table = MyDT
                    .Sort = " UId ASC"
                End With
                MyDT = dtView.ToTable
                Dim Userid As String = String.Empty
                Dim total As Decimal = 0
                Dim Num As Integer = 0
                Dim i As Integer = 0
                Dim Comm As Decimal = 0
                For Each dtrow As Data.DataRow In MyDT.Rows
                    If Userid = "" Then     'by using the raw data, how to calculate from there? Need to show Romeo and get the calculation method, the lister and broker are different.
                        'may need to get the commission at the very beginning, which is here.
                        Userid = dtrow.Item("UId").ToString
                        total = CDbl(dtrow.Item("AgencyFee").ToString) + CDbl(dtrow.Item("DisburesementFee").ToString) + _
                                CDbl(dtrow.Item("TAS").ToString) + CDbl(dtrow.Item("ListerAmt").ToString) + _
                                CDbl(dtrow.Item("GovAmt").ToString) - CDbl(dtrow.Item("PListerAmt").ToString) - _
                                CDbl(dtrow.Item("SDuty").ToString) - CDbl(dtrow.Item("Reff").ToString) - _
                                CDbl(dtrow.Item("PGovAmt").ToString)
                        Num = 1
                    Else
                        If Userid <> dtrow.Item("UId").ToString Then
                            Comm = getCommission(total, Userid)
                            GrandTotal = GrandTotal + (total * Comm / 100)
                            For j As Integer = i To Num - 1
                                'get commission percentage using the total
                                'update to every affected row
                                MyDT.Rows(j)(13) = Comm
                                MyDT.AcceptChanges()
                            Next
                            Userid = dtrow.Item("UId").ToString
                            total = CDbl(dtrow.Item("AgencyFee").ToString) + CDbl(dtrow.Item("DisburesementFee").ToString) + _
                                CDbl(dtrow.Item("TAS").ToString) + CDbl(dtrow.Item("ListerAmt").ToString) + _
                                CDbl(dtrow.Item("GovAmt").ToString) - CDbl(dtrow.Item("PListerAmt").ToString) - _
                                CDbl(dtrow.Item("SDuty").ToString) - CDbl(dtrow.Item("Reff").ToString) - _
                                CDbl(dtrow.Item("PGovAmt").ToString)
                            i = Num
                            Num = Num + 1
                        Else
                            total = total + CDbl(dtrow.Item("AgencyFee").ToString) + CDbl(dtrow.Item("DisburesementFee").ToString) + _
                                CDbl(dtrow.Item("TAS").ToString) + CDbl(dtrow.Item("ListerAmt").ToString) + _
                                CDbl(dtrow.Item("GovAmt").ToString) - CDbl(dtrow.Item("PListerAmt").ToString) - _
                                CDbl(dtrow.Item("SDuty").ToString) - CDbl(dtrow.Item("Reff").ToString) - _
                                CDbl(dtrow.Item("PGovAmt").ToString)
                            Num = Num + 1
                        End If
                    End If
                Next
                Return MyDT
            End If
        Else
            lblMsgError.Text = "Please select a date range!"
            divError.Visible = True
            Return Nothing
            Exit Function
        End If
    End Function

    Private Function LoadMonthlyReportNew(ByRef GrandTotal As Decimal) As Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        Dim TempDT As New Data.DataTable

        Dim strWhere As String

        Dim dtFromDate As DateTime
        Dim dtToDate As DateTime
        'to add in filtering for report - fromDate, toDate, Type(Created;Updated)
        If txtFromDate.Text <> "" And txtToDate.Text <> "" Then
            dtFromDate = Date.ParseExact(txtFromDate.Text, "dd-MMM-yyyy", Nothing)
            dtToDate = Date.ParseExact(txtToDate.Text, "dd-MMM-yyyy", Nothing)
            If dtFromDate > dtToDate Then
                lblMsgError.Text = "Date To cannot be earlier than Date From!"
                divError.Visible = True
                Return Nothing
                Exit Function
            Else
                If ddlStaffName.SelectedValue = "" Then '--All
                    strWhere = ""
                Else
                    strWhere = " AND (st.ListerId=@StaffId OR st.TransactorId=@StaffId)"
                    objDB.AddParameter("StaffId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlStaffName.SelectedValue))
                End If
                objDB.AddParameter("DateFrom", Data.SqlDbType.DateTime, dtFromDate)
                objDB.AddParameter("DateTo", Data.SqlDbType.DateTime, dtToDate.AddDays(1))
                objDB.OpenConnection()
                objDB.dbVariable.SQLString = "SELECT st.CollectCompleteDate, p.UnitNumber + ', ' + d.Road + " & _
                                        "', ' + o.Location + ', ' + s.State + ', ' + y.Country as 'Address', " & _
                                        "CASE st.TransListType WHEN '1' THEN 'SR' WHEN '2' THEN 'SC' WHEN '3' " & _
                                        "THEN 'SL' WHEN '4' THEN 'SI' ELSE '' END as Cat, st.ListerId, st.TransactorId, " & _
                                        "l.Initial as Lister, t.Initial as Transactor, st.AgencyFee, st.GovAmt, " & _
                                        "st.GstPaidBy, st.DisbursementFee, 0 as TotalTA, " & _
                                        "0 as StampDuty, st.ListerAmt, st.ReferralAmt FROM AW_SaleTransaction st " & _
                                        "LEFT JOIN AW_Property p ON st.PropertyId = p.PropertyId " & _
                                        "LEFT JOIN AW_Road d ON p.RoadId = d.RoadId LEFT JOIN AW_Location o " & _
                                        "ON d.LocationId = o.LocationId LEFT JOIN AW_State s ON o.StateId = s.StateId " & _
                                        "LEFT JOIN AW_Country y ON s.CountryId=y.CountryId LEFT JOIN AW_Staff l " & _
                                        "ON st.ListerId = l.StaffId LEFT JOIN AW_Staff t " & _
                                        "ON st.TransactorId = t.StaffId WHERE st.Status in ('3','4') " & _
                                        "AND st.UndoDate IS NULL AND st.CollectComplete = 1 " & _
                                        "AND st.CollectCompleteDate BETWEEN @DateFrom AND @DateTo " & strWhere & "UNION " & _
                                        "SELECT st.CollectCompleteDate, p.UnitNumber + ', ' + d.Road + ', ' + o.Location + " & _
                                        "', ' + s.State + ', ' + y.Country as 'Address', " & _
                                        "CASE st.TransListType WHEN '1' THEN 'RR' WHEN '2' THEN 'RC' WHEN '3' " & _
                                        "THEN 'RL' WHEN '4' THEN 'RI' ELSE '' END as Cat, st.ListerId, st.TransactorId, " & _
                                        "l.Initial as Lister, t.Initial as Transactor, st.AgencyFee, st.GovAmt, st.GstPaidBy, " & _
                                        "st.DisbursementFee, st.TotalTA, st.StampDuty, st.ListerAmt, st.ReferralAmt " & _
                                        "FROM AW_RentTransaction st LEFT JOIN AW_Property p ON " & _
                                        "st.PropertyId = p.PropertyId LEFT JOIN AW_Road d ON p.RoadId = d.RoadId " & _
                                        "LEFT JOIN AW_Location o ON d.LocationId = o.LocationId LEFT JOIN AW_State s " & _
                                        "ON o.StateId = s.StateId LEFT JOIN AW_Country y ON s.CountryId=y.CountryId " & _
                                        "LEFT JOIN AW_Staff l ON st.ListerId = l.StaffId LEFT JOIN AW_Staff t " & _
                                        "ON st.TransactorId = t.StaffId WHERE st.Status in ('3','4') AND " & _
                                        "st.UndoDate IS NULL AND st.CollectComplete = 1 AND st.CollectDate " & _
                                        "BETWEEN @DateFrom AND @DateTo " & strWhere
                objDB.FillData(TempDT, "AW_MonthlyRenReport", Database.SQLCommandType.NormalString)
                objDB.CloseConnection()
                'Massage data before push to RDLC
                'Declare Table and column
                Dim MyDT As New Data.DataTable
                Dim Name As Data.DataColumn = New Data.DataColumn("Name")
                Name.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(Name)  '0
                Dim Address As Data.DataColumn = New Data.DataColumn("Address")
                Address.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(Address)   '1
                Dim Cat As Data.DataColumn = New Data.DataColumn("Cat")
                Cat.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(Cat)   '2
                Dim AgencyFee As Data.DataColumn = New Data.DataColumn("AgencyFee")
                AgencyFee.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(AgencyFee) '3
                Dim DisbursementFee As Data.DataColumn = New Data.DataColumn("DisburesementFee")
                DisbursementFee.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(DisbursementFee)   '4
                Dim TAS As Data.DataColumn = New Data.DataColumn("TAS")
                TAS.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(TAS)   '5
                Dim ListerAmt As Data.DataColumn = New Data.DataColumn("ListerAmt")
                ListerAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(ListerAmt) '6
                Dim GovAmt As Data.DataColumn = New Data.DataColumn("GovAmt")
                GovAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(GovAmt)    '7
                Dim PListerAmt As Data.DataColumn = New Data.DataColumn("PListerAmt")
                PListerAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(PListerAmt)    '8
                Dim SDuty As Data.DataColumn = New Data.DataColumn("SDuty")
                SDuty.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(SDuty) '9
                Dim Reff As Data.DataColumn = New Data.DataColumn("Reff")
                Reff.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(Reff)  '10
                Dim PGovAmt As Data.DataColumn = New Data.DataColumn("PGovAmt")
                PGovAmt.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add(PGovAmt)   '11
                Dim UId As Data.DataColumn = New Data.DataColumn("UId")
                UId.DataType = System.Type.GetType("System.String")
                MyDT.Columns.Add(UId)   '12
                Dim Commission As Data.DataColumn = New Data.DataColumn("Commission")
                Commission.DataType = System.Type.GetType("System.Decimal")
                MyDT.Columns.Add("Commission")  '13
                Dim MyRow As Data.DataRow
                For Each dr As Data.DataRow In TempDT.Rows
                    If dr.Item("Lister").ToString = dr.Item("Transactor").ToString Then
                        MyRow = MyDT.NewRow()
                        MyRow(0) = dr.Item("Lister").ToString
                        MyRow(1) = dr.Item("Address").ToString
                        MyRow(2) = dr.Item("Cat").ToString
                        MyRow(3) = CDbl(dr.Item("AgencyFee").ToString)
                        MyRow(4) = CDbl(dr.Item("DisbursementFee").ToString)
                        MyRow(5) = CDbl(dr.Item("TotalTA").ToString) + CDbl(dr.Item("StampDuty").ToString)
                        MyRow(6) = 0
                        If dr.Item("GstPaidBy").ToString = "1" Then
                            MyRow(7) = CDbl(dr.Item("GovAmt").ToString)
                        Else
                            MyRow(7) = 0
                        End If
                        MyRow(8) = 0
                        MyRow(9) = CDbl(dr.Item("StampDuty").ToString)
                        MyRow(10) = CDbl(dr.Item("ReferralAmt").ToString)
                        MyRow(11) = CDbl(dr.Item("GovAmt").ToString)
                        MyRow(12) = dr.Item("ListerId").ToString
                        MyRow(13) = 0
                        MyDT.Rows.Add(MyRow)
                    Else
                        'add as lister
                        MyRow = MyDT.NewRow()
                        MyRow(0) = dr.Item("Lister").ToString
                        MyRow(1) = dr.Item("Address").ToString
                        MyRow(2) = dr.Item("Cat").ToString
                        MyRow(3) = 0
                        MyRow(4) = 0
                        MyRow(5) = 0
                        MyRow(6) = CDbl(dr.Item("ListerAmt").ToString)
                        MyRow(7) = 0
                        MyRow(8) = 0
                        MyRow(9) = 0
                        MyRow(10) = 0
                        MyRow(11) = 0
                        MyRow(12) = dr.Item("ListerId").ToString
                        MyRow(13) = 0
                        MyDT.Rows.Add(MyRow)
                        'add as transactor
                        MyRow = MyDT.NewRow()
                        MyRow(0) = dr.Item("Transactor").ToString
                        MyRow(1) = dr.Item("Address").ToString
                        MyRow(2) = dr.Item("Cat").ToString
                        MyRow(3) = CDbl(dr.Item("AgencyFee").ToString)
                        MyRow(4) = CDbl(dr.Item("DisbursementFee").ToString)
                        MyRow(5) = CDbl(dr.Item("TotalTA").ToString) + CDbl(dr.Item("StampDuty").ToString)
                        MyRow(6) = CDbl(dr.Item("ListerAmt").ToString)
                        If dr.Item("GstPaidBy").ToString = "1" Then
                            MyRow(7) = CDbl(dr.Item("GovAmt").ToString)
                        Else
                            MyRow(7) = 0
                        End If
                        MyRow(8) = CDbl(dr.Item("ListerAmt").ToString)
                        MyRow(9) = CDbl(dr.Item("StampDuty").ToString)
                        MyRow(10) = CDbl(dr.Item("ReferralAmt").ToString)
                        MyRow(11) = CDbl(dr.Item("GovAmt").ToString)
                        MyRow(12) = dr.Item("TransactorId").ToString
                        MyRow(13) = 0
                        MyDT.Rows.Add(MyRow)
                    End If
                Next
                MyDT.TableName = "AW_MonthlyReport"
                'Process calculation and get commission percentage
                Dim dtView As Data.DataView = New Data.DataView
                With dtView
                    .Table = MyDT
                    .Sort = " UId ASC"
                End With
                MyDT = dtView.ToTable
                Dim Userid As String = String.Empty
                Dim total As Decimal = 0
                Dim Num As Integer = 0
                Dim i As Integer = 0
                Dim Comm As Decimal = 0
                For Each dtrow As Data.DataRow In MyDT.Rows
                    If Userid = "" Then     'by using the raw data, how to calculate from there? Need to show Romeo and get the calculation method, the lister and broker are different.
                        'may need to get the commission at the very beginning, which is here.
                        Userid = dtrow.Item("UId").ToString
                        total = CDbl(dtrow.Item("AgencyFee").ToString) + CDbl(dtrow.Item("DisburesementFee").ToString) + _
                                CDbl(dtrow.Item("TAS").ToString) + CDbl(dtrow.Item("ListerAmt").ToString) + _
                                CDbl(dtrow.Item("GovAmt").ToString) - CDbl(dtrow.Item("PListerAmt").ToString) - _
                                CDbl(dtrow.Item("SDuty").ToString) - CDbl(dtrow.Item("Reff").ToString) - _
                                CDbl(dtrow.Item("PGovAmt").ToString)
                        Num = 1
                    Else
                        If Userid <> dtrow.Item("UId").ToString Then
                            Comm = getCommission(total, Userid)
                            GrandTotal = GrandTotal + (total * Comm / 100)
                            For j As Integer = i To Num - 1
                                'get commission percentage using the total
                                'update to every affected row
                                MyDT.Rows(j)(13) = Comm
                                MyDT.AcceptChanges()
                            Next
                            Userid = dtrow.Item("UId").ToString
                            total = CDbl(dtrow.Item("AgencyFee").ToString) + CDbl(dtrow.Item("DisburesementFee").ToString) + _
                                CDbl(dtrow.Item("TAS").ToString) + CDbl(dtrow.Item("ListerAmt").ToString) + _
                                CDbl(dtrow.Item("GovAmt").ToString) - CDbl(dtrow.Item("PListerAmt").ToString) - _
                                CDbl(dtrow.Item("SDuty").ToString) - CDbl(dtrow.Item("Reff").ToString) - _
                                CDbl(dtrow.Item("PGovAmt").ToString)
                            i = Num
                            Num = Num + 1
                        Else
                            total = total + CDbl(dtrow.Item("AgencyFee").ToString) + CDbl(dtrow.Item("DisburesementFee").ToString) + _
                                CDbl(dtrow.Item("TAS").ToString) + CDbl(dtrow.Item("ListerAmt").ToString) + _
                                CDbl(dtrow.Item("GovAmt").ToString) - CDbl(dtrow.Item("PListerAmt").ToString) - _
                                CDbl(dtrow.Item("SDuty").ToString) - CDbl(dtrow.Item("Reff").ToString) - _
                                CDbl(dtrow.Item("PGovAmt").ToString)
                            Num = Num + 1
                        End If
                    End If
                Next
                Return MyDT
            End If
        Else
            lblMsgError.Text = "Please select a date range!"
            divError.Visible = True
            Return Nothing
            Exit Function
        End If
    End Function

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session.Item("StaffId") Is Nothing Then
            Response.Redirect("default.aspx")
        End If

        If Not Page.IsPostBack Then

            lblStaffUID.Text = Session.Item("StaffId").ToString
            lblStaffType.Text = Session.Item("StaffType").ToString

            GetddlListBy()
        End If
    End Sub

    Private Sub GetddlListBy()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT StaffId,Name FROM AW_Staff WHERE StatusDelete = 0 ORDER BY Name"
        objDB.FillData(TempDT, "AW_Staff", Database.SQLCommandType.NormalString)

        objDB.CloseConnection()

        With ddlStaffName
            .DataSource = TempDT
            .DataTextField = "Name"
            .DataValueField = "StaffId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim TempDT As New Data.DataTable
        Dim CommissionDT As New Data.DataTable
        Dim GrandTotal As Decimal = 0

        TempDT = LoadMonthlyReport(GrandTotal)

        'CommissionDT = LoadCommission()

        'Test by Tan Chin Teong 20120809
        'Dim objRDLC As New RDLC("report/TestReport.rdlc", "MonthlyReport")
        'objRDLC.ExportReport(RDLC.ExportTypeFormat.PDF, HttpContext.Current)


        If TempDT IsNot Nothing Then
            Dim objRDLC As New RDLC("report/rptMonthlyReport.rdlc", "MonthlyReport")
            TempDT.TableName = "dsMonthlyReport_AW_MonthlyReport"
            objRDLC.AddParameter("GrandTotal", GrandTotal)
            objRDLC.AddParameter("FromDate", txtFromDate.Text)
            objRDLC.AddParameter("ToDate", txtToDate.Text)
            'CommissionDT.TableName = "dsMonthlyReport_AW_Commission"
            objRDLC.AddDatabase(TempDT)
            objRDLC.AddDatabase(CommissionDT)
            'objRDLC.ExportReport(RDLC.ExportTypeFormat.PDF, HttpContext.Current)
            With Me.ReportViewer1.LocalReport
                .ReportPath = "report/rptCommissionReport.rdlc"
                .DataSources.Clear()
            End With
            Dim rptDataSource As ReportDataSource
            Dim rptDataSource2 As ReportDataSource
            rptDataSource = New ReportDataSource("dsMonthlyReport_AW_MonthlyReport", TempDT)
            rptDataSource2 = New ReportDataSource("dsCommissionReport_AW_MonthlyReport", TempDT)
            Me.ReportViewer1.LocalReport.DataSources.Add(rptDataSource)
            Me.ReportViewer1.LocalReport.DataSources.Add(rptDataSource2)
            Dim p1 As ReportParameter = New ReportParameter("FromDate", txtFromDate.Text)
            Dim p2 As ReportParameter = New ReportParameter("ToDate", txtToDate.Text)
            ReportViewer1.ShowParameterPrompts = True
            ReportViewer1.LocalReport.SetParameters(p1)
            ReportViewer1.LocalReport.SetParameters(p2)
        End If
    End Sub

    Protected Sub btnResetSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnResetSearch.Click
        ClearAllFields()
    End Sub

    Private Sub ClearAllFields()
        txtFromDate.Text = String.Empty
        txtToDate.Text = String.Empty
        ddlStaffName.SelectedIndex = 0
    End Sub

    Private Function getCommission(ByVal Total As Decimal, ByVal UserId As String) As Decimal
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        Dim TempDT As New Data.DataTable
        Dim commission As Decimal

        objDB.OpenConnection()
        objDB.dbVariable.SQLString = "SELECT Commission FROM AW_Commission WHERE StaffId=@StaffId AND @Total >=AmountFrom AND @Total <=AmountTo"
        objDB.AddParameter("StaffId", Data.SqlDbType.UniqueIdentifier, New Guid(UserId))
        objDB.AddParameter("Total", Data.SqlDbType.Decimal, Total)
        objDB.FillData(TempDT, "AW_Commission", Database.SQLCommandType.NormalString)
        objDB.CloseConnection()
        For Each dtrow As Data.DataRow In TempDT.Rows
            If dtrow.Item("Commission").ToString = "" Then
                commission = 0
            Else
                commission = CDbl(dtrow.Item("Commission").ToString)
            End If
        Next
        Return commission
    End Function
End Class
